﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;

namespace TestJQGrid
{
    public class GenerateSqlStatement
    {
        #region initialize
        private List<string> select = new List<String>();
        private List<string> displayName = new List<String>();
        private List<string> selectGroupBy = new List<string>();
        private List<string> from = new List<String>();
        private string where = null;
        private List<string> groupBy = new List<String>();
        private string having = null;
        private List<string> AggrFunctionList = new List<string>();

        public GenerateSqlStatement()
        {
            AggrFunctionList.Add("");
            AggrFunctionList.Add("AVG");
            AggrFunctionList.Add("SUM");
            AggrFunctionList.Add("MIN");
            AggrFunctionList.Add("MAX");
            AggrFunctionList.Add("COUNT");
        }
        #endregion

        #region get/validate generated query
        public string getQuery()
        {
            //if query/its fields are valid then return null
            if (!checkIndividualElements()) return null;
            if (!checkWholeQuery()) return null;
            //else crate query and return
            return getFormattedQuery(false);
        }

        private string getFormattedQuery(bool isTesting)
        {
            string query;
            bool isGroupBy = false;

            //generate select clause - consider the groupBy applied on them
            string selectClause = "";
            for (int i = 0; i < select.Count; i++)
            {
                if (String.IsNullOrEmpty(selectGroupBy[i])) //not group by
                {
                    if (String.IsNullOrEmpty(selectClause)) // is it first field
                        selectClause = select[i] ;
                    else
                        selectClause = selectClause + " , " + select[i];
                }
                else //group by
                {
                    isGroupBy = true;
                    if (String.IsNullOrEmpty(selectClause)) // is it first field
                        selectClause = selectGroupBy[i] + " ( " + select[i] + " ) ";
                    else
                        selectClause = selectClause + " , " + selectGroupBy[i] + " ( " + select[i] + " ) ";
                }

                //set the display name
                selectClause += " AS " + displayName[i] + " ";
            }
            query = " SELECT " + selectClause;

            //generate from clause
            query += " FROM " + string.Join(",", from);

            //add where, only if not null
            if (isTesting)
            {
                if (string.IsNullOrEmpty(where))
                    query += " WHERE ('0' = '1') ";
                else
                {
                    string whereM = where.Replace("''", "'");
                    query += " WHERE " + whereM + " AND ('0' = '1') ";
                }
            }
            else if (!string.IsNullOrEmpty(where))
            {
                query += " WHERE " + where;
            }

            //add groupBy stuff
            if (groupBy.Count != 0 || isGroupBy)
            {
                //add specified fields to groupBy
                //also add selected field in groupBy, as per groupBy sql requirement
                string grpBy = string.Join(",", groupBy);

                foreach (string str in select)
                {
                    if (!groupBy.Contains(str))
                        if (String.IsNullOrEmpty(grpBy))
                            grpBy = str;
                        else
                            grpBy += "," + str;
                }
                query += " GROUP BY " + grpBy;

                //add having part also
                if (!string.IsNullOrEmpty(having))
                    query += " HAVING " + having;
            }
            return query;
        }

        private bool checkIndividualElements()
        {
            //select & from are must
            if (select.Count == 0) return false;
            if (from.Count == 0) return false;

            //where is optional
            //if (string.IsNullOrEmpty(where)) return false;

            //if having present then groupBy must be present
            if (!string.IsNullOrEmpty(having) && (groupBy.Count == 0)) return false;

            return true;
        }

        private bool checkWholeQuery()
        {
            string query = getFormattedQuery(true);
            try
            {
                string returnValue = GlobalMethods.ExecuteScalar(query);
            }
            catch (NullReferenceException)
            {
                return true;
            }
            catch (SqlException ex)
            {
                throw new Exception("Invalid Query : \n" + query + "\n Reason: \n" + ex.Errors.ToString() + "\n");
            }

            return true;
        }
        #endregion

        #region simple select statement
        public void addSelect(string column, string dispName, string groupByFunction = "")
        {
            //check if column is already added - if not then add that column
            if (select.Contains(column)) return;

            //input format is - tableName.columnName
            string[] field = column.Split('.');
            if (field.Length != 2)
                throw new Exception("Invalid FieldName:" + column + ". Valid format is TableName.FieldName");

            //add table & its where clause if not existing
            addTable(field[0].ToString());

            //add column to select & save groupby (it should be a valid formula)
            select.Add(column);
            if (!AggrFunctionList.Contains(groupByFunction))
                throw new Exception("Invalid groupBy function : " + groupByFunction + ".");
            selectGroupBy.Add(groupByFunction);
            
            //save the displayname
            displayName.Add(dispName);
        }

        private void addTable(string tableName)
        {
            //if table is existing then ignore addition
            if (from.Contains(tableName)) return;

            //if this is the first table - then add and return
            if (from.Count == 0)
            {
                from.Add(tableName);
                return;
            }

            //if there is already a table present (it will different from input table - see the first line)
            //then add the relation of that existing table first and then add relationship with the "tableName"
            string defaultTable = "CarSalesData";
            if ((from.Count == 1) && (!from.Contains(defaultTable)))
            {
                addTableRelation(defaultTable, from[0].ToString());
                from.Add(defaultTable);

                //this condition is to avoid double addition of product table
                //the new table we were trying to add was product table itself
                //which we have added just now, so no need to contiue.
                if (defaultTable == tableName)
                    return;
            }
            addTableRelation(defaultTable, tableName);
            from.Add(tableName);
        }

        private void addTableRelation(string productTable, string tableName)
        {
            //now add the "tableName" relationship
            string query = "SELECT [WhereClause] FROM [TableRelation] WHERE PrimaryTable = '" + productTable + "' AND RelatedTable = '" + tableName + "'";
            string whereClause = GlobalMethods.ExecuteScalar(query);

            //check validity
            if (string.IsNullOrEmpty(whereClause))
                throw new Exception("No Relation Exist between Tables:" + productTable + " AND " + tableName + ".");

            //add it to where clause
            addWhere(whereClause);
        }
        #endregion

        #region adding where condition
        public void addCondition(string column, string oper, string value)
        {
            if (oper == "CONTAINS")
                addWhere(" " + oper + "(" + column + ",'" + value + "') ");
            else
                addWhere(" " + column + " " + oper + " ''" + value + "'' ");
        }

        private void addWhere(string clause)
        {
            if (string.IsNullOrEmpty(where))
                where = " ( " + clause + " ) ";
            else
                where += " AND ( " + clause + " ) ";
        }
        #endregion

        #region add groupBy condition
        public void addGroup(string column, string displayName)
        {
            groupBy.Add(column);
            addSelect(column, displayName);
        }
        #endregion    }
    }
}